package HomeWork.Question5;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Scanner;

public class MySQL_jdbc{
    public static final String URL = "jdbc:mysql://47.120.46.251:3306/mydatabases";
    private final static String USERNAME = "Jin-OK";
    private final static String PASSWD = "admin";
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }
        try {
            Connection con = DriverManager.getConnection(URL,USERNAME,PASSWD);
            Statement sta = con.createStatement();
            while(true){
                System.out.println("--------欢迎进入图书借阅系统--------\n请输入书名的关键字进行查询:");
                Scanner sc = new Scanner(System.in);
                String book_name = sc.next();
                if(book_name.equals("exit"))
                    break;
                ResultSet rs = sta.executeQuery("SELECT * FROM tb_book WHERE book_name LIKE '%"+book_name+"%'");
                if(!rs.isBeforeFirst()){
                    System.out.println("没有找到相关书籍！");
                    continue;
                }
                else{
                    System.out.println("编号\t｜\t书名\t｜\t作者\t｜\t单价\t｜\t当前数量\t｜\t描述");
                    while(rs.next()) {
                        System.out.println(
                            rs.getString(1)+"\t｜\t"+          // 输出第一列
                            rs.getString(2)+"\t｜\t"+           // 输出第二列
                            rs.getString(3)+"\t｜\t"+ 
                            rs.getString(4)+"\t｜\t"+ 
                            rs.getString(5)+"\t｜\t"+ 
                            rs.getString(6));
                    }
                    rs.close();
                }
                System.out.println("是否需要借书？输入Y或N：");
                if(sc.next().equals("Y")){
                    System.out.println("请输入想要借阅书本的编号：");
                    String id = sc.next();
                    ResultSet set = sta.executeQuery("SELECT book_num FROM tb_book WHERE book_id = '"+id+"'");
                    if(set.isBeforeFirst()){
                        set.next();
                        if(set.getInt(1)>0){
                            sta.executeUpdate("UPDATE tb_book SET book_num = book_num-1 WHERE book_id = '"+id+"'");
                            System.out.println("借阅成功！");                            
                        }else
                            System.out.println("没有书了！");
                    }else
                        System.out.println("没有这本书！");
                set.close();
            }
        }
            sta.close();
            con.close(); 
        }catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！");
        }
        
    }
}
